package com.advanced;

import com.advanced.pojo.Employee;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * ClassName: JDBCAdvanced
 * Description:
 *我们要把数据存储在一个载体里，这个载体就是实体类！
 * @Author liu
 * @Create 2025/4/23 14:50
 * @Version 1.0
 */
public class JDBCAdvanced {
//ORM（Object Relational Mapping）思想，对象到关系数据库的映射
    //ORM 模拟
    @Test
    public void testORM() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root",  "15178102072Liu");

        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp where emp_id = ?");
        preparedStatement.setInt(1,1);

        ResultSet resultSet = preparedStatement.executeQuery();

        Employee employee = null;

        if(resultSet.next()){
            employee = new Employee();
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");

            //为对象的属性赋值
            employee.setEmpId(empId);
            employee.setEmpName(empName);
            employee.setEmpsalary(empSalary);
            employee.setEmpAge(empAge);

            System.out.println(employee);

            resultSet.close();
            preparedStatement.close();
            connection.close();


        }
    }

@Test
public void testORMList() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

    PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp");

    ResultSet resultSet = preparedStatement.executeQuery();

    Employee employee = null;

    List<Employee> employeeList = new ArrayList<>();

    if(resultSet.next()){
        employee  = new Employee();
        int empId = resultSet.getInt("emp_id");
        String empName = resultSet.getString("emp_name");
        double empSalary = resultSet.getDouble("emp_salary");
        int empAge = resultSet.getInt("emp_age");

        employee.setEmpId(empId);
        employee.setEmpName(empName);
        employee.setEmpsalary(empSalary);
        employee.setEmpAge(empAge);

        //将每次循环封装的一行数据的对象存储于集合中
        employeeList.add(employee);
    }

    //处理结果，遍历集合
    for(Employee emp : employeeList){
        System.out.println(emp);
    }
    resultSet.close();
    preparedStatement.close();
    connection.close();



}
    //主键回显
    //就比如：你在进行软件的注册，会有手机号，验证码，密码等等，然后注册结束后，进行用户名设置，就是主键回显，
    //因为你再注册后，你的信息就存储在MySQL中，然后你进行用户名的修改，就会对原有的信息之内存中进行修改
@Test
public void testReturnPk() throws SQLException {
    Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

    String sql ="INSERT INTO t_emp(emp_name,emp_salary,emp_age) VALUES (?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

    Employee employee = new Employee( null,"jack",123.45,29);//emp_id 为 null,因为id一定会修改的

    preparedStatement.setString(1,employee.getEmpName());
    preparedStatement.setDouble(2,employee.getEmpsalary());
    preparedStatement.setInt(3,employee.getEmpAge());

    //因为新增，所以用executeUpdate();
    int result = preparedStatement.executeUpdate();
    ResultSet resultSet = null;//用于判断

    if(result >0){
        System.out.println("成功");

        //获取当前新增数据的主键列，回显到Java中employee对象的empId 属性上
        //返回的主键值，是一个单行单列的结果存储在ResultSet里
        resultSet = preparedStatement.getGeneratedKeys();
        if(resultSet.next()){
            int empId = resultSet.getInt(1);
            employee.setEmpId(empId);
        }
        System.out.println(employee);
    }else{
        System.out.println("失败");
    }

if(resultSet !=null){
    resultSet.close();
}
preparedStatement.close();
connection.close();

}

//批量操作
// - 插入多条数据时，一条一条发送给数据库执行，效率低下！
//- 通过批量操作，可以提升多次操作效率！
    @Test
    public void testMoreInsert() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

        String sql = "insert into t_emp (emp_name,emp_salary,emp_age) values (?,?,?)";

        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            //为占位符赋值
            preparedStatement.setString(1, "marry"+i);
            preparedStatement.setDouble(2, 100.0+i);
            preparedStatement.setInt(3, 20+i);

            preparedStatement.executeUpdate();
        }

        long end = System.currentTimeMillis();

        System.out.println("消耗时间："+(end - start));

        preparedStatement.close();
        connection.close();

    }
    //批量操作（优化版）
    @Test
    public void testBatch() throws Exception {
        //1.注册驱动
//        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?rewriteBatchedStatements=true", "root", "15178102072Liu");

        //3.编写SQL语句
        /*
            注意：1、必须在连接数据库的URL后面追加?rewriteBatchedStatements=true，允许批量操作
                2、新增SQL必须用values。且语句最后不要追加;结束
                3、调用addBatch()方法，将SQL语句进行批量添加操作
                4、统一执行批量操作，调用executeBatch()
         */
        String sql = "insert into t_emp (emp_name,emp_salary,emp_age) values (?,?,?)";

        //4.创建预编译的PreparedStatement，传入SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //获取当前行代码执行的时间。毫秒值
        long start = System.currentTimeMillis();
        for(int i = 0;i<10000;i++){
            //5.为占位符赋值
            preparedStatement.setString(1, "marry"+i);
            preparedStatement.setDouble(2, 100.0+i);
            preparedStatement.setInt(3, 20+i);

            preparedStatement.addBatch();
        }

        //执行批量操作
        preparedStatement.executeBatch();

        long end = System.currentTimeMillis();

        System.out.println("消耗时间："+(end - start));

        preparedStatement.close();
        connection.close();
    }
}
